package com.scuec.groupfive.mapper;

import com.scuec.groupfive.dto.ExamScoreStatsDTO;
import org.apache.ibatis.annotations.*;
import java.util.List;

public interface ExamScoreStatsMapper {

    @Select("<script>" +
            "SELECT " +
            "exam_type, " +
            "SUM(CASE WHEN score BETWEEN 0 AND 20 THEN 1 ELSE 0 END) AS range_0_20_count, " +
            "ROUND(SUM(CASE WHEN score BETWEEN 0 AND 20 THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS range_0_20_percent, " +
            "SUM(CASE WHEN score BETWEEN 21 AND 40 THEN 1 ELSE 0 END) AS range_21_40_count, " +
            "ROUND(SUM(CASE WHEN score BETWEEN 21 AND 40 THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS range_21_40_percent, " +
            "SUM(CASE WHEN score BETWEEN 41 AND 60 THEN 1 ELSE 0 END) AS range_41_60_count, " +
            "ROUND(SUM(CASE WHEN score BETWEEN 41 AND 60 THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS range_41_60_percent, " +
            "SUM(CASE WHEN score BETWEEN 61 AND 70 THEN 1 ELSE 0 END) AS range_61_70_count, " +
            "ROUND(SUM(CASE WHEN score BETWEEN 61 AND 70 THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS range_61_70_percent, " +
            "SUM(CASE WHEN score BETWEEN 71 AND 80 THEN 1 ELSE 0 END) AS range_71_80_count, " +
            "ROUND(SUM(CASE WHEN score BETWEEN 71 AND 80 THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS range_71_80_percent, " +
            "COUNT(*) AS total_count " +
            "FROM past_exam_records " +
            "WHERE 1=1 " +
            "<if test='examType != null and examType != \"all\"'>" +
            "AND exam_type = #{examType} " +
            "</if>" +
            "<if test='examType == null or examType == \"all\"'>" +
            "AND exam_type IN ('数一', '数二', '数三') " +
            "</if>" +
            "GROUP BY exam_type" +
            "</script>")
    @Results({
            @Result(property = "examType", column = "exam_type"),
            @Result(property = "range0_20Count", column = "range_0_20_count"),
            @Result(property = "range0_20Percent", column = "range_0_20_percent"),
            @Result(property = "range21_40Count", column = "range_21_40_count"),
            @Result(property = "range21_40Percent", column = "range_21_40_percent"),
            @Result(property = "range41_60Count", column = "range_41_60_count"),
            @Result(property = "range41_60Percent", column = "range_41_60_percent"),
            @Result(property = "range61_70Count", column = "range_61_70_count"),
            @Result(property = "range61_70Percent", column = "range_61_70_percent"),
            @Result(property = "range71_80Count", column = "range_71_80_count"),
            @Result(property = "range71_80Percent", column = "range_71_80_percent"),
            @Result(property = "totalCount", column = "total_count")
    })
    List<ExamScoreStatsDTO> getScoreDistributionByExamType(@Param("examType") String examType);
}